package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.Member;
import com.pignest.hotel.dao.MemberDao;
import com.pignest.hotel.definition.MemberState;
import com.pignest.hotel.util.WhereCreator;

public class MemberHibernateDao extends BaseHibernateDao implements MemberDao {

	@SuppressWarnings("unchecked")
	public List<Member> query(final String name, final String mobile,final List<String> sex,final List<String> type,final String mail,final String cardNo){
		
		return getHibernateTemplate().executeFind(new HibernateCallback() {

           @Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				WhereCreator where =new WhereCreator();
				boolean hasName = StringUtils.isNotEmpty(name);
				boolean hasMobile = StringUtils.isNotEmpty(mobile);
				boolean hasCardNo = StringUtils.isNotEmpty(cardNo);
				String hassex="";
				String hasType="";
				if(sex!=null){
                 hassex =where.getInstr(sex);
				}
                //System.out.println("hassex="+hassex+"name="+name);
				if(type!=null)
				  hasType=where.getInstr(type);;//会员类型
                boolean hasMail= StringUtils.isNotEmpty(mail);
                
				StringBuilder hql = new StringBuilder("from Member as b where 1=1");
				if(hasName)
					hql.append(" and b.name like :name");
				if(hasMobile)
					hql.append(" and b.mobile like :mobile");
				if(hasMail)
					hql.append(" and b.mail like :mail");
				if(hasCardNo)
					hql.append(" and b.cardNo like :cardNo");
				if(!hassex.equals(""))
					hql.append(" and b.sex in (:sex)");
				if(!hasType.equals(""))
					hql.append(" and b.type in (:type)");
				Query q = session.createQuery(hql.toString());
				if (hasName)
					q.setString("name", '%' + name.replaceAll("%", "%%") + '%');
				if (hasMobile)
					q.setString("mobile", '%' + mobile.replaceAll("%", "%%") + '%');
				if (hasMail)
					q.setString("mail", '%' + mail.replaceAll("%", "%%") + '%');
				if (hasCardNo)
					q.setString("cardNo", '%' + cardNo.replaceAll("%", "%%") + '%');
				if(!hassex.equals(""))
					q.setString("sex", hassex);
				if(!hasType.equals(""))
					q.setString("type", hasType);
				return q.list();
			}
		});
	}
	/**
	 * 计算当前会员余额
	 * ***/
	 public int queryMemberBalance(Member member)
	 {
		 StringBuilder hql = new StringBuilder();
	     int i=0;
		 hql.append("select sum(price) from Memberchargehistrory b where b.member.id='"+member.getId()+"'");
		 List r = getHibernateTemplate().find(hql.toString());
		 if(r!=null&&r.size()!=0){
		  	 Object s= r.get(0);
		  	 if(s!=null)
		  		i=Integer.valueOf(s.toString());
		    }
		return i;
	 }
	 /**查询单个会员*/
	 public Member queryMember(String name,String cardNo,String mobile,String sex,String cerNo )
	 {
		 StringBuilder hql = new StringBuilder();
		 hql.append(" from Member b where 1=1");
		   if(cardNo!=null)
		      hql.append(" and b.cardNo='").append(cardNo).append("'");
		   if(name !=null)
			   hql.append(" and b.name like '%").append(name).append("%'");
		   if(mobile!=null&&!mobile.equals(""))
			   hql.append(" and b.mobile like '%").append(mobile).append("%'");
		   if(cerNo!=null&&!cerNo.equals(""))
			   hql.append(" and b.certificateNo like '%").append(cerNo).append("%'");;
		   if(sex!=null&&!sex.equals(""))
			   hql.append(" and b.sex='").append(sex).append("'");
		   hql.append(" and b.state='").append(MemberState.OnUsed.name()).append("'");
		   List <Member> mList =getHibernateTemplate().find(hql.toString());
		   if(mList!=null&&mList.size()==1)
		   {
			   return (Member)mList.get(0);
		   }
		   else
			   return null;
	 }
	 /***计算会员历史消费总额*/
	 public int countMemberHistoryUsed(Member m,boolean isPositive) {
		 StringBuilder hql = new StringBuilder();
	     int i=0;
		 hql.append("select sum(price) from Memberchargehistrory b where b.member.id='"+m.getId()+"'");
		 hql.append(" and price<0");
		 List r = getHibernateTemplate().find(hql.toString());
		 if(r!=null&&r.size()!=0){
		  	 Object s= r.get(0);
		  	 if(s!=null)
		  		i=Integer.valueOf(s.toString());
		    }
		 if(isPositive)
			 return -i;
		 else
			 return i;
	}
	 
	@SuppressWarnings("unchecked")
	public List<Member> listMembers(final String name, final String cardNo, final String phone, final int pageSize, final int pageIndex) {
		 return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder();
				boolean n = !StringUtils.isEmpty(name);
				boolean c = !StringUtils.isEmpty(cardNo);
				boolean p = !StringUtils.isEmpty(phone);
				if (n) {
					hql.append("o.name like :name");
				}
				if (c) {
					if (hql.length() > 0)
						hql.append(" and ");
					hql.append("o.cardNo like :card");
				}
				if (p) {
					if (hql.length() > 0)
						hql.append(" and ");
					hql.append("o.mobile like :phone");
				}
				if (hql.length() > 0) {
					hql.insert(0, "from Member o where ");
				} else {
					hql.insert(0, "from Member o ");
				}
				Query q = session.createQuery(hql.toString());
				if (n)
					q.setString("name", "%" + name + "%");
				if (c)
					q.setString("card", "%" + cardNo + "%");
				if (p)
					q.setString("phone", "%" + phone + "%");
				if (pageSize > 0 && pageIndex >= 0) {
					q.setFirstResult(pageSize * pageIndex);
					q.setFetchSize(pageSize);
				}
				return q.list();
			}
		});
	 }

}
